﻿
using System;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Rogan.DBUtility;
using Rogan.Common.Model;

// ReSharper disable once CheckNamespace
namespace Rogan.Dal
{
    //SysUserDal
    // ReSharper disable once PartialTypeWithSinglePart
    public partial class SysUserDal
    {

        public bool Exists(int UserId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Sys_User");
            strSql.Append(" where ");
            strSql.Append(" UserId = @UserId  ");
            SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.Int,4)
			};
            parameters[0].Value = UserId;

            return DbHelper.Exists(strSql.ToString(), parameters);
        }



        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(SysUser model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Sys_User(");
            strSql.Append("LoginrName,LoginPassword,DeptID,RealName,Title,Sex,Phone,Address,Note,IsEnable");
            strSql.Append(") values (");
            strSql.Append("@LoginrName,@LoginPassword,@DeptID,@RealName,@Title,@Sex,@Phone,@Address,@Note,@IsEnable");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@LoginrName", SqlDbType.VarChar,50) ,            
                        new SqlParameter("@LoginPassword", SqlDbType.VarChar,50) ,            
                        new SqlParameter("@DeptID", SqlDbType.Int,4) ,            
                        new SqlParameter("@RealName", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@Title", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@Sex", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@Phone", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@Address", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@Note", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@IsEnable", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.LoginrName;
            parameters[1].Value = model.LoginPassword;
            parameters[2].Value = model.DeptID;
            parameters[3].Value = model.RealName;
            parameters[4].Value = model.Title;
            parameters[5].Value = model.Sex;
            parameters[6].Value = model.Phone;
            parameters[7].Value = model.Address;
            parameters[8].Value = model.Note;
            parameters[9].Value = model.IsEnable;

            object obj = DbHelper.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }

            return Convert.ToInt32(obj);



        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(SysUser model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Sys_User set ");

            strSql.Append(" LoginrName = @LoginrName , ");
            strSql.Append(" LoginPassword = @LoginPassword , ");
            strSql.Append(" DeptID = @DeptID , ");
            strSql.Append(" RealName = @RealName , ");
            strSql.Append(" Title = @Title , ");
            strSql.Append(" Sex = @Sex , ");
            strSql.Append(" Phone = @Phone , ");
            strSql.Append(" Address = @Address , ");
            strSql.Append(" Note = @Note , ");
            strSql.Append(" IsEnable = @IsEnable  ");
            strSql.Append(" where UserId=@UserId ");

            SqlParameter[] parameters = {
			            new SqlParameter("@UserId", SqlDbType.Int,4) ,            
                        new SqlParameter("@LoginrName", SqlDbType.VarChar,50) ,            
                        new SqlParameter("@LoginPassword", SqlDbType.VarChar,50) ,            
                        new SqlParameter("@DeptID", SqlDbType.Int,4) ,            
                        new SqlParameter("@RealName", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@Title", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@Sex", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@Phone", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@Address", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@Note", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@IsEnable", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.UserId;
            parameters[1].Value = model.LoginrName;
            parameters[2].Value = model.LoginPassword;
            parameters[3].Value = model.DeptID;
            parameters[4].Value = model.RealName;
            parameters[5].Value = model.Title;
            parameters[6].Value = model.Sex;
            parameters[7].Value = model.Phone;
            parameters[8].Value = model.Address;
            parameters[9].Value = model.Note;
            parameters[10].Value = model.IsEnable;
            int rows = DbHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            return false;

        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int UserId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Sys_User ");
            strSql.Append(" where UserId=@UserId");
            SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.Int,4)
			};
            parameters[0].Value = UserId;


            int rows = DbHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            return false;

        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool DeleteList(string UserIdlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Sys_User");
            strSql.Append(" where UserId in (" + UserIdlist + ")  ");
            int rows = DbHelper.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            return false;

        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public SysUser GetModel(int UserId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select UserId, LoginrName, LoginPassword, DeptID, RealName, Title, Sex, Phone, Address, Note, IsEnable  ");
            strSql.Append("  from Sys_User ");
            strSql.Append(" where UserId=@UserId");
            SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.Int,4)
			};
            parameters[0].Value = UserId;


            DataSet ds = DbHelper.Query(strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            return null;

        }

        /// <summary>
        /// DataRow转成实体对象
        /// </summary>
        public SysUser DataRowToModel(DataRow dataRow)
        {
            SysUser model = new SysUser();
            if (dataRow["UserId"].ToString() != "")
            {
                model.UserId = int.Parse(dataRow["UserId"].ToString());
            }
            model.LoginrName = dataRow["LoginrName"].ToString();
            model.LoginPassword = dataRow["LoginPassword"].ToString();
            if (dataRow["DeptID"].ToString() != "")
            {
                model.DeptID = int.Parse(dataRow["DeptID"].ToString());
            }
            model.RealName = dataRow["RealName"].ToString();
            model.Title = dataRow["Title"].ToString();
            model.Sex = dataRow["Sex"].ToString();
            model.Phone = dataRow["Phone"].ToString();
            model.Address = dataRow["Address"].ToString();
            model.Note = dataRow["Note"].ToString();
            if (dataRow["IsEnable"].ToString() != "")
            {
                if ((dataRow["IsEnable"].ToString() == "1") || (dataRow["IsEnable"].ToString().ToLower() == "true"))
                {
                    model.IsEnable = true;
                }
                else
                {
                    model.IsEnable = false;
                }
            }

            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM Sys_User ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelper.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataTable GetList(int top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (top > 0)
            {
                strSql.Append(" top " + top);
            }
            strSql.Append(" * ");
            strSql.Append(" FROM Sys_User ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelper.Query(strSql.ToString()).Tables[0];
        }
    }
}

